package com.cogent.dao.sql;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.cogent.common.util.MapUtil;
import com.cogent.common.util.StringUtil;
import com.cogent.dao.domain.ColumnDomain;
import com.cogent.dao.domain.ParamDomain;
import com.cogent.dao.domain.QueryParamDomain;
import com.cogent.dao.domain.SqlDomain;
import com.cogent.dao.domain.TableInfoDomain;
import com.cogent.dao.orm.DBOperator;
import com.cogent.dao.orm.IDGen;
import com.cogent.dao.type.QueryType;

public abstract class AbstractSqlBuilder implements ISqlBuilder{
	private static Map CACHE_TABLE_COLUMN_TYPE = new HashMap();
	
	private DBOperator dbOperator;
	private IDGen idgen;
	 
	public SqlDomain insertSql(String tableName, HashMap valMap) {
		SqlDomain sqlDomain = new SqlDomain();
		List<ParamDomain> param = new ArrayList<ParamDomain>();
		TableInfoDomain domain = getDbOperator().getColumnInfo(tableName);
		Iterator it = domain.getColumnList().iterator();
		String id = "";
		if(MapUtil.isEmpty(valMap, domain.getId())) {
			id = idgen.getNextID(tableName, domain);
			valMap.put(domain.getId(), id);
		}else
			id = MapUtil.getStringValue(valMap, domain.getId());
		sqlDomain.setId(id);		
		StringBuffer str = new StringBuffer();
		str.append("INSERT INTO ").append(tableName).append("(");
		StringBuffer str2 = new StringBuffer();
		
		Map curFieldMap = dbOperator.getDBFieldMappedType();
		while(it.hasNext()) {
			ColumnDomain columnDomain = (ColumnDomain)it.next();
			if(valMap.containsKey(columnDomain.getColumnName())) {
				boolean paramflag = true;
				if(MapUtil.getIntValue(curFieldMap, columnDomain.getColumnType(), 0) ==  3) {
					String curDateValue = MapUtil.getStringValue(valMap, columnDomain.getColumnName());
					if(curDateValue.equals(QueryType.DB_SYSDATE)) {
						str2.append(getDbDateFunction()).append(",");
						paramflag = false;
					}
				}
				str.append(columnDomain.getColumnName()).append(",");
				if(paramflag) {
					str2.append("?,");
					ParamDomain paramDomain = new ParamDomain();
					paramDomain.setColumntype(columnDomain.getColumnType());
					paramDomain.setColumnvalue(MapUtil.getObjectValue(valMap, columnDomain.getColumnName()));
					param.add(paramDomain);
				}
			}
		}
		String sql = "";
		if(str.length() > 0)
			sql = str.substring(0, str.length()-1) + ") VALUES (";
		if(str2.length() > 0)
			sql += str2.substring(0, str2.length()-1) + ")";
		sqlDomain.setSql(sql);
		sqlDomain.setParam(param);
		
		return sqlDomain;
	}
	
	public SqlDomain updateSql(String tableName, HashMap valMap, String id) {
		TableInfoDomain domain = getDbOperator().getColumnInfo(tableName);
		domain.setId(id);
		return updateSql(domain, tableName, valMap);
	}
	
	public SqlDomain updateSql(String tableName, HashMap valMap) {
		TableInfoDomain domain = getDbOperator().getColumnInfo(tableName);
		return updateSql(domain, tableName, valMap);
	}
	
	private SqlDomain updateSql(TableInfoDomain domain, String tableName, HashMap valMap) {
		SqlDomain sqlDomain = new SqlDomain();
		List<ParamDomain> param = new ArrayList<ParamDomain>();		
		Iterator it = domain.getColumnList().iterator();
		StringBuffer str = new StringBuffer();
		String idType = "";
		str.append("UPDATE ").append(tableName).append(" SET ");
		Map curFieldMap = dbOperator.getDBFieldMappedType();
		while(it.hasNext()) {			
			ColumnDomain columnDomain = (ColumnDomain)it.next();
			if(columnDomain.getColumnName().equals(domain.getId())) {
				idType = columnDomain.getColumnType();
				continue;
			}
			if(valMap.containsKey(columnDomain.getColumnName())) {
				boolean paramflag = true;
				if(MapUtil.getIntValue(curFieldMap, columnDomain.getColumnType(), 0) ==  3) {
					String curDateValue = MapUtil.getStringValue(valMap, columnDomain.getColumnName());
					if(curDateValue.equals(QueryType.DB_SYSDATE)) {
						str.append(columnDomain.getColumnName()).append("=").append(getDbDateFunction()).append(",");
						paramflag = false;
					}
				}
				if(paramflag) {
					str.append(columnDomain.getColumnName()).append("=?,");
					ParamDomain paramDomain = new ParamDomain();
					paramDomain.setColumntype(columnDomain.getColumnType());
					paramDomain.setColumnvalue(MapUtil.getObjectValue(valMap, columnDomain.getColumnName()));
					param.add(paramDomain);
				}
			}
		}
		String sql = str.substring(0, str.length()-1) + 
			" WHERE " + domain.getId() + "=?";
		ParamDomain paramDomain = new ParamDomain();
		paramDomain.setColumntype(idType);
		paramDomain.setColumnvalue(MapUtil.getObjectValue(valMap, domain.getId()));
		param.add(paramDomain);
		sqlDomain.setSql(sql);
		sqlDomain.setParam(param);
		sqlDomain.setId(MapUtil.getStringValue(valMap, domain.getId()));
		
		return sqlDomain;
	}
	
	public SqlDomain insertOrUpdateSql(String tableName, HashMap valMap) {
		TableInfoDomain domain = getDbOperator().getColumnInfo(tableName);
		if((MapUtil.isEmpty(valMap, domain.getId())) || 
				(MapUtil.getStringValue(valMap, domain.getId()).equals("0"))) {
			valMap.remove(domain.getId());
			return insertSql(tableName, valMap);
		}else
			return updateSql(tableName, valMap);
	}
	
	public SqlDomain querySql(String tableName, List<QueryParamDomain> valList) {
		SqlDomain sqlDomain = new SqlDomain();
		List<ParamDomain> param = new ArrayList<ParamDomain>();
		StringBuffer str = new StringBuffer();
		str.append("SELECT * FROM ").append(tableName);
		Iterator it = valList.iterator();
		boolean isWhere = false;
		while(it.hasNext()) {			
			QueryParamDomain queryParamDomain = (QueryParamDomain)it.next();
			if(!StringUtil.isEmpty(queryParamDomain.getCurValue())) {
				String type = getType(tableName, queryParamDomain.getTableField().toLowerCase());
				if(type != null) {
					if(isWhere)
						str.append(" AND ");
					else {
						str.append(" WHERE ");
						isWhere = true;
					}
					if(queryParamDomain.getQueryType() == QueryType.INARRAY) {
						str.append(queryParamDomain.getTableField()).append(QueryType.getQueryString(queryParamDomain.getQueryType()));
						String[] temp = queryParamDomain.getCurValue().split(",");
						int length = temp.length - 1;
						for(int i=0; i<temp.length; i++) {
							str.append("?");
							ParamDomain paramDomain = new ParamDomain();
							paramDomain.setColumntype(type);
							paramDomain.setColumnvalue(temp[i]);
							param.add(paramDomain);
							if(i < length)
								str.append(",");
						}
						str.append(")");
					} else if(queryParamDomain.getQueryType() == QueryType.LIKEINARRAY) {
						str.append("(");
						String[] temp = queryParamDomain.getCurValue().split(",");
						int length = temp.length - 1;
						for(int i=0; i<temp.length; i++) {
							str.append(queryParamDomain.getTableField()).append(QueryType.getQueryString(queryParamDomain.getQueryType()));
							ParamDomain paramDomain = new ParamDomain();
							paramDomain.setColumntype(type);
							paramDomain.setColumnvalue(QueryType.getParamValue(queryParamDomain.getQueryType(), temp[i]));
							param.add(paramDomain);
							if(i < length)
								str.append(" or ");
						}
						str.append(")");
					} else {
						str.append(queryParamDomain.getTableField()).append(QueryType.getQueryString(queryParamDomain.getQueryType()));
						ParamDomain paramDomain = new ParamDomain();
						paramDomain.setColumntype(type);
						paramDomain.setColumnvalue(QueryType.getParamValue(queryParamDomain.getQueryType(), queryParamDomain.getCurValue()));
						param.add(paramDomain);
					}
				}
			}
		}
		sqlDomain.setSql(str.toString());
		sqlDomain.setParam(param);
		
		return sqlDomain;
	}
	
	public List<ParamDomain> getParamList(List<QueryParamDomain> paramList) {
		List<ParamDomain> param = new ArrayList<ParamDomain>();
		Iterator it = paramList.iterator();
		while(it.hasNext()) {
			QueryParamDomain queryParamDomain = (QueryParamDomain)it.next();
			if(!StringUtil.isEmpty(queryParamDomain.getCurValue())) {
				String type = getType(queryParamDomain.getTableName().toLowerCase()
						, queryParamDomain.getTableField().toLowerCase());				
				ParamDomain paramDomain = new ParamDomain();
				paramDomain.setColumntype(type);
				paramDomain.setColumnvalue(QueryType.getParamValue(queryParamDomain.getQueryType(), queryParamDomain.getCurValue()));
				param.add(paramDomain);	
			}
		}
		return param;
	}
	
	public SqlDomain queryWithPrefixSql(String prefixSql, List<QueryParamDomain> valList) {
		SqlDomain sqlDomain = new SqlDomain();
		List<ParamDomain> param = new ArrayList<ParamDomain>();
		StringBuffer str = new StringBuffer();
		str.append(prefixSql);
		boolean isWhere = false;
		String temp = prefixSql.toLowerCase();
		if(temp.indexOf(" where ") > 0)
			isWhere = true;
		Iterator it = valList.iterator();
		while(it.hasNext()) {
			QueryParamDomain queryParamDomain = (QueryParamDomain)it.next();
			if(!StringUtil.isEmpty(queryParamDomain.getCurValue())) {
				String type = getType(queryParamDomain.getTableName().toLowerCase()
						, queryParamDomain.getTableField().toLowerCase());
				if(type != null) {
					if(isWhere)
						str.append(" AND ");
					else {
						str.append(" WHERE ");
						isWhere = true;
					}
					if(queryParamDomain.getQueryType() == QueryType.INARRAY) {
						str.append(queryParamDomain.getTableField()).append(QueryType.getQueryString(queryParamDomain.getQueryType()));
						String[] curtemp = queryParamDomain.getCurValue().split(",");
						int length = curtemp.length - 1;
						for(int i=0; i<curtemp.length; i++) {
							str.append("?");
							ParamDomain paramDomain = new ParamDomain();
							paramDomain.setColumntype(type);
							paramDomain.setColumnvalue(curtemp[i]);
							param.add(paramDomain);
							if(i < length)
								str.append(",");
						}
						str.append(")");
					} else if(queryParamDomain.getQueryType() == QueryType.LIKEINARRAY) {
						str.append("(");
						String[] curtemp = queryParamDomain.getCurValue().split(",");
						int length = curtemp.length - 1;
						for(int i=0; i<curtemp.length; i++) {
							str.append(queryParamDomain.getTableField()).append(QueryType.getQueryString(queryParamDomain.getQueryType()));
							ParamDomain paramDomain = new ParamDomain();
							paramDomain.setColumntype(type);
							paramDomain.setColumnvalue(QueryType.getParamValue(queryParamDomain.getQueryType(), curtemp[i]));
							param.add(paramDomain);
							if(i < length)
								str.append(" or ");
						}
						str.append(")");
					} else {
						str.append(getMapField(queryParamDomain.getTableField(), queryParamDomain.getMapedFileld()))
							.append(QueryType.getQueryString(queryParamDomain.getQueryType()));
						ParamDomain paramDomain = new ParamDomain();
						paramDomain.setColumntype(type);
						paramDomain.setColumnvalue(QueryType.getParamValue(queryParamDomain.getQueryType(), queryParamDomain.getCurValue()));
						param.add(paramDomain);
					}
				}
			}
		}
		sqlDomain.setSql(str.toString());
		sqlDomain.setParam(param);
		
		return sqlDomain;
	}
	
	public SqlDomain deleteSql(String tableName, List<QueryParamDomain> valList) {
		SqlDomain sqlDomain = new SqlDomain();
		List<ParamDomain> param = new ArrayList<ParamDomain>();
		StringBuffer str = new StringBuffer();
		str.append("DELETE FROM ").append(tableName).append(" WHERE ");
		Iterator it = valList.iterator();
		boolean isand = false;
		while(it.hasNext()) {			
			QueryParamDomain queryParamDomain = (QueryParamDomain)it.next();
			if(!StringUtil.isEmpty(queryParamDomain.getCurValue())) {
				String type = getType(tableName, queryParamDomain.getTableField().toLowerCase());
				if(type != null) {
					if(isand)
						str.append(" AND ");
					else
						isand = true;
					if(queryParamDomain.getQueryType() == QueryType.INARRAY) {
						str.append(queryParamDomain.getTableField()).append(QueryType.getQueryString(queryParamDomain.getQueryType()));
						String[] curtemp = queryParamDomain.getCurValue().split(",");
						int length = curtemp.length - 1;
						for(int i=0; i<curtemp.length; i++) {
							str.append("?");
							ParamDomain paramDomain = new ParamDomain();
							paramDomain.setColumntype(type);
							paramDomain.setColumnvalue(curtemp[i]);
							param.add(paramDomain);
							if(i < length)
								str.append(",");
						}
						str.append(")");
					} else if(queryParamDomain.getQueryType() == QueryType.LIKEINARRAY) {
						str.append("(");
						String[] curtemp = queryParamDomain.getCurValue().split(",");
						int length = curtemp.length - 1;
						for(int i=0; i<curtemp.length; i++) {
							str.append(queryParamDomain.getTableField()).append(QueryType.getQueryString(queryParamDomain.getQueryType()));
							ParamDomain paramDomain = new ParamDomain();
							paramDomain.setColumntype(type);
							paramDomain.setColumnvalue(QueryType.getParamValue(queryParamDomain.getQueryType(), curtemp[i]));
							param.add(paramDomain);
							if(i < length)
								str.append(" or ");
						}
						str.append(")");
					} else {
						str.append(queryParamDomain.getTableField()).append("=?");
						ParamDomain paramDomain = new ParamDomain();
						paramDomain.setColumntype(type);
						paramDomain.setColumnvalue(queryParamDomain.getCurValue());
						param.add(paramDomain);
					}
				}
			}
		}		
		sqlDomain.setSql(str.toString());
		sqlDomain.setParam(param);
		
		return sqlDomain;
	}
	
	protected String getType(String tableName, String tableCol) {
		Map curMap = null;
		if(CACHE_TABLE_COLUMN_TYPE.containsKey(tableName))
			curMap = (Map)CACHE_TABLE_COLUMN_TYPE.get(tableName);			
		else {
			TableInfoDomain domain = getDbOperator().getColumnInfo(tableName);
			Iterator it = domain.getColumnList().iterator();
			curMap = new HashMap();
			while(it.hasNext()) {			
				ColumnDomain columnDomain = (ColumnDomain)it.next();
				curMap.put(columnDomain.getColumnName(), columnDomain.getColumnType());
			}
			CACHE_TABLE_COLUMN_TYPE.put(tableName, curMap);
		}
		
		if(curMap.containsKey(tableCol))
			return MapUtil.getStringValue(curMap, tableCol);
		else
			return null;
	}
	
	protected abstract String getDbDateFunction();
	
	private String getMapField(String field, String maped) {
		if(StringUtil.isEmpty(maped))
			return field;
		else
			return maped;
	}
	
	public DBOperator getDbOperator() {
		return dbOperator;
	}

	public void setDbOperator(DBOperator dbOperator) {
		this.dbOperator = dbOperator;
	}

	public IDGen getIdgen() {
		return idgen;
	}

	public void setIdgen(IDGen idgen) {
		this.idgen = idgen;
	}
}
